package cn.fjw.webservice;

import cn.fjw.webservice.model.City;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;
import javax.servlet.http.HttpServlet;
import java.beans.Statement;
import java.io.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class Init extends HttpServlet implements ServletContextListener {
    @Override
    public void contextInitialized(ServletContextEvent servletContextEvent) {
        DataSource dataSource=new DataSource();
        Connection connection=dataSource.getConnection();
        try {
            connection.prepareStatement("drop table city if exists ").executeUpdate();
            connection.prepareStatement("create table city(id int primary key,name varchar,englishName varchar, abbr varchar " +
                    ",postCode varchar ,parent int,lv int ,path varchar )").executeUpdate();
//            connection.prepareStatement("insert into ttt(id,name,sex,age) values (1,'jiawei','man',12)").execute();
//            ResultSet rs = connection.prepareStatement("select * from ttt").executeQuery();
//            while (rs.next()){
//                System.out.println(rs.getInt(1));
//                System.out.println(rs.getString(2));
//                System.out.println(rs.getString(3));
//                System.out.println(rs.getInt(4));
//            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        String path = this.getClass().getClassLoader().getResource("cities.txt").getPath();
        try {
            BufferedReader bufferedReader = new BufferedReader(new InputStreamReader(new FileInputStream(path), "utf-8"));
            String line;
            List<Integer> provinces=new ArrayList<Integer>();
            for (int i=1;(line = bufferedReader.readLine()) != null;i++) {
                String[] ary = line.split(",");

                String sql = "insert into city values (?,?,?,?,?,?,?,?)";
                PreparedStatement statement=connection.prepareStatement(sql);
                statement.setInt(1,i);
                statement.setString(2,ary[1]);
                statement.setString(3,ary[3]);
                statement.setString(4,ary[4]);
                statement.setString(5,ary[2]);

//
//                City city = new City();
//                city.setEnglishName(ary[3]);
//                city.setAbbreviation(ary[4]);
//                city.setName(ary[1]);
//                city.setPostCode(ary[2]);
                if (ary[0].equals(ary[1])) {
                    statement.setInt(6,0);
                    statement.setInt(7,1);
                    statement.setString(8,"/"+i);
                    provinces.add(i);
//                    city.setParent(null);
                } else {
                    PreparedStatement ps = connection.prepareStatement("select * from city where name=?");
                    ps.setString(1,ary[0]);
                    ResultSet rs=ps.executeQuery();
                    if(rs.next()){
                        statement.setInt(6,rs.getInt("id"));
                        statement.setInt(7,rs.getInt("lv")+1);
                        statement.setString(8,rs.getString("path")+"/"+i);
                    }
                    rs.close();
                    ps.close();
//                    for (Iterator ite = province.keySet().iterator(); ite.hasNext(); ) {
//                        City parent = province.get(ite.next());
//                        if (ary[0].equals(parent.getName())) {
//                            city.setParent(parent);
//                            parent.getChildren().add(city);
//                        }
//                    }
                }
                statement.executeUpdate();
                statement.close();
            }
//            GlobalVar.PROVINCES = province;
//            PreparedStatement testPs = connection.prepareStatement("select * from city");
//            ResultSet testRs = testPs.executeQuery();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
//        String[][] ary = {
//
//        };

    }

    @Override
    public void contextDestroyed(ServletContextEvent servletContextEvent) {

    }
}
